<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class pro_kontrol_air extends MY_Controller {
	
	function __construct() {
		parent::__construct(); 
		
		$this->load->model('pro_kontrol_air_model', 'KontrolPemakaian');
		$this->secure_session();
	}

	function index() {
		$data['title'] = "Kontrol Pemakaian Air";
		$data['comboLokasi']=$this->KontrolPemakaian->getLokasi();
		$data['it_app']=$this->KontrolPemakaian->GetApp();

		$data['nama_tenant'] = '';
		$data['jenis_usaha'] = '';

		$date = date('m-Y');
		$date_explode=explode('-', $date);
		  if($date_explode[0]<='09'){
	      $y1 = substr($date_explode[0], 1, 1);
	      //var_dump($y1);

	      $hasil1 = "";
	      $iromawi1 =
	      array("","Januari","Februari","Maret","April","Mei","Juni","Juli","Agustus","September");
	      if(array_key_exists($y1,$iromawi1)){
	        $hasil1 = $iromawi1[$y1];
	      }

	      $tgl_new1 = $hasil1." - ".$date_explode[1]." ";

	    } else {

	      $hasil1 = "";
	      $iromawi1 =
	      array("","","","","","","","","","","Oktober","November","Desember");
	      if(array_key_exists($date_explode[0],$iromawi1)){
	        $hasil1 = $iromawi1[$date_explode[0]];
	      }

	      $tgl_new1 = $hasil1." - ".$date_explode[1]." ";

	    }
	      $data['periode_penagihan']=$tgl_new1;
		viewByTemplate('pro_kontrol_air/index', $data, 'grid');
	}

	function getNoAuto() {
		$query1 = $this->db->query("SELECT id_pemakaian_air FROM (SELECT * FROM pro_pemakaian_air ORDER BY id_pemakaian_air DESC) WHERE rownum <=1");
				 				if ($query1->row_array()) {
				 					$getquery1 = $query1->row_array();
				 					$rowcount1 = $getquery1['id_pemakaian_air'];
				 					$autonum1 = $rowcount1 + 1;
				 				} else {
								$autonum1 = 1;
							}
		$data['no_auto'] = $autonum1;

		//var_dump($data['lokasi_tempat']);
		echo json_encode($data);
	}


	function getdata() {
		$this->secure_session();
		echo $this->KontrolPemakaian->GetJSON();
	}

	function getdata2() {
		$this->secure_session();
		echo $this->KontrolPemakaian->GetJSON2();	
	}

	function getNoToko($type=null) {
		$id = $this->input->post('option');
		
		//$this->secure_session();

		$ls_sql	= "	SELECT 	a.id_lokasi ,
							b.lokasi_tempat,
							a.no_toko 
					FROM pro_tenant a 
					JOIN pro_lokasi_tempat b ON a.id_lokasi = b.id_lokasi
					WHERE a.status_tenant = 'Tenant' AND a.lokasi_tempat = '".$id."'
					ORDER BY REGEXP_REPLACE(a.no_toko, '[0-9]', ''), TO_NUMBER(REGEXP_SUBSTR(a.no_toko, '[0-9]+')) ";

		$result	= $this->db->query($ls_sql);
		$data_toko = $result->result_array();
		
		//var_dump($data_toko);
		echo json_encode($data_toko);

		//echo $this->KontrolPemakaian->getNotoko($id);	
	}

	function getLokasiTempat($type=null) {
		$id = $this->input->post('option');
		
		//$this->secure_session();

		$ls_sql1	= "	SELECT DISTINCT a.lokasi_tempat, a.id_lokasi
						FROM pro_tenant a 
						WHERE a.status_tenant = 'Tenant'
						ORDER BY a.id_lokasi ASC";

		$result1	= $this->db->query($ls_sql1);
		$data_lokasi = $result1->result_array();

		echo json_encode($data_lokasi);

		//echo $this->KontrolPemakaian->getNotoko($id);	
	}

	/*function getNoTokoEdit($type=null) {
		$id = $this->input->post('option');
		//var_dump($id);
		//$this->secure_session();

		$ls_sql	= "	SELECT 	b.id_lokasi as key_lokasi,
							b.lokasi_tempat as id_lokasi,
							a.no_toko as no_toko
					FROM pro_tenant a 
					JOIN pro_lokasi_tempat b ON a.id_lokasi = b.id_lokasi
					WHERE a.status_tenant = 'Tenant' AND a.lokasi_tempat = '".$id."'
					ORDER BY REGEXP_REPLACE(a.no_toko, '[0-9]', ''), TO_NUMBER(REGEXP_SUBSTR(a.no_toko, '[0-9]+'))";

		$result	= $this->db->query($ls_sql);
		$data_toko = $result->row_array(); 

		echo json_encode($data_toko);

		//echo $this->KontrolPemakaian->getNotoko($id);	
	}*/

	function getLoopCheck(){

		$lokasi_tempat = $this->input->post('lokasi_tempat');
		$id_lokasi = $this->input->post('id_lokasi');
		$no_toko = $this->input->post('no_toko');
		$check_no_toko = $this->KontrolPemakaian->check_no_toko($lokasi_tempat);;
		$kondisi_check = $check_no_toko['limit_no_toko'];
		$kondisi_no_toko = $no_toko;

		//var_dump($kondisi_check);
		//var_dump($kondisi_no_toko);
		$q_kondisi_lokasi_tempat = $this->db->query("SELECT b.id_lokasi as limit_id_lokasi, b.lokasi_tempat FROM (
					SELECT id_lokasi, lokasi_tempat 
					FROM pro_tenant  
					WHERE status_tenant = 'Tenant'
					ORDER BY id_lokasi DESC) b
					WHERE ROWNUM <=1");
		
		$r_data_lokasi = $q_kondisi_lokasi_tempat->row_array();

		$kondisi_lokasi_tempat = $r_data_lokasi['limit_id_lokasi'];

		if($id_lokasi == $kondisi_lokasi_tempat){
			$data['mode'] = 'SwitchAll';
		} else {
			if($kondisi_check === $kondisi_no_toko){

				$data['mode'] = 'Switch';

			} else {

				$data['mode'] ='Normal';
				
			}
		}
			echo json_encode($data);
	}

	function getLoopSwitch(){
		$query1 = $this->db->query("SELECT id_pemakaian_air FROM (SELECT * FROM pro_pemakaian_air ORDER BY id_pemakaian_air DESC) WHERE rownum <=1");
				 				if ($query1->row_array()) {
				 					$getquery1 = $query1->row_array();
				 					$rowcount1 = $getquery1['id_pemakaian_air'];
				 					$autonum1 = $rowcount1 + 1;
				 				} else {
								$autonum1 = 1;
							}
		$data['no_auto'] = $autonum1;

		$set_lokasi = $this->input->post('set_lokasi');
		$set_no_toko = $this->input->post('set_no_toko');
		$id_lokasi = $this->input->post('set_id_lokasi');
		//var_dump($id_lokasi);
		//$id_lokasi = '3';
		$ls_sql1 = "SELECT b.id_lokasi as limit_id_lokasi, b.lokasi_tempat FROM (
					SELECT id_lokasi, lokasi_tempat 
					FROM pro_tenant  
					WHERE status_tenant = 'Tenant' AND id_lokasi > '".$id_lokasi."'
					ORDER BY id_lokasi ASC) b
					WHERE ROWNUM <=1 ";

		$result1	= $this->db->query($ls_sql1);
		$data_lokasi = $result1->row_array();
		
		$lokasi_tempat_loop = $data_lokasi['lokasi_tempat'];

		$ls_sql2	= "	SELECT no_toko 
						FROM pro_tenant  
						WHERE status_tenant = 'Tenant' AND lokasi_tempat = '".$lokasi_tempat_loop."'
						ORDER BY case when regexp_instr(NO_TOKO,'[0-9]+') = 1 then 
									 to_number(regexp_substr(NO_TOKO,'[0-9]+')) 
								else 
									 null 
								end ASC, NO_TOKO ASC ";

		$result2	= $this->db->query($ls_sql2);
		$data_toko 	= $result2->result_array();

		$ls_sql4	= "	SELECT b.no_toko FROM (
						SELECT no_toko 
						FROM pro_tenant  
						WHERE status_tenant = 'Tenant' AND lokasi_tempat = '".$lokasi_tempat_loop."'
						ORDER BY case when regexp_instr(NO_TOKO,'[0-9]+') = 1 then 
									 to_number(regexp_substr(NO_TOKO,'[0-9]+')) 
								else 
									 null 
								end ASC, NO_TOKO ASC) b
						WHERE ROWNUM <=1  ";

		$result4			= $this->db->query($ls_sql4);
		$data_toko_limit 	= $result4->row_array();
		$no_toko_tenant = $data_toko_limit['no_toko'];

		$ls_sql3	= "	SELECT nama_tenant, jenis_usaha
						FROM pro_tenant  
						WHERE status_tenant = 'Tenant' AND lokasi_tempat = '".$lokasi_tempat_loop."' AND no_toko = '".$no_toko_tenant."' ";

		$result3	= $this->db->query($ls_sql3);
		$data_tenant 	= $result3->row_array();

		$data['no_toko'] = $data_toko;
		$data['no_toko_limit'] =$data_toko_limit['no_toko'];
		$data['nama_tenant'] = $data_tenant['nama_tenant'];
		$data['jenis_usaha'] = $data_tenant['jenis_usaha'];
		$data['lokasi_tempat'] = $data_lokasi['lokasi_tempat'];
		$data['limit_id_lokasi'] = $data_lokasi['limit_id_lokasi'];
		$data['mode'] = 'switch';
		//var_dump($data['mode']);

		echo json_encode($data);
	}
	
	function getLoopSwitchAll(){
		$query1 = $this->db->query("SELECT id_pemakaian_air FROM (SELECT * FROM pro_pemakaian_air ORDER BY id_pemakaian_air DESC) WHERE rownum <=1");
				 				if ($query1->row_array()) {
				 					$getquery1 = $query1->row_array();
				 					$rowcount1 = $getquery1['id_pemakaian_air'];
				 					$autonum1 = $rowcount1 + 1;
				 				} else {
								$autonum1 = 1;
							}
		$data['no_auto'] = $autonum1;

		$set_lokasi = $this->input->post('set_lokasi');
		$set_no_toko = $this->input->post('set_no_toko');
		$id_lokasi = $this->input->post('set_id_lokasi');
		//var_dump($id_lokasi);
		//$id_lokasi = '3';
		$ls_sql1 = "SELECT b.id_lokasi as limit_id_lokasi, b.lokasi_tempat FROM (
					SELECT id_lokasi, lokasi_tempat 
					FROM pro_tenant  
					WHERE status_tenant = 'Tenant'
					ORDER BY id_lokasi ASC) b
					WHERE ROWNUM <=1 ";

		$result1	= $this->db->query($ls_sql1);
		$data_lokasi = $result1->row_array();

		$lokasi_tempat_loop = $data_lokasi['lokasi_tempat'];

		$ls_sql2	= "	SELECT no_toko 
						FROM pro_tenant  
						WHERE status_tenant = 'Tenant' AND lokasi_tempat = '".$lokasi_tempat_loop."'
						ORDER BY case when regexp_instr(NO_TOKO,'[0-9]+') = 1 then 
									 to_number(regexp_substr(NO_TOKO,'[0-9]+')) 
								else 
									 null 
								end ASC, NO_TOKO ASC ";

		$result2	= $this->db->query($ls_sql2);
		$data_toko 	= $result2->result_array();

		$ls_sql4	= "	SELECT b.no_toko FROM (
						SELECT no_toko 
						FROM pro_tenant  
						WHERE status_tenant = 'Tenant' AND lokasi_tempat = '".$lokasi_tempat_loop."'
						ORDER BY case when regexp_instr(NO_TOKO,'[0-9]+') = 1 then 
									 to_number(regexp_substr(NO_TOKO,'[0-9]+')) 
								else 
									 null 
								end ASC, NO_TOKO ASC) b
						WHERE ROWNUM <=1  ";

		$result4			= $this->db->query($ls_sql4);
		$data_toko_limit 	= $result4->row_array();
		$no_toko_tenant = $data_toko_limit['no_toko'];

		$ls_sql3	= "	SELECT nama_tenant, jenis_usaha
						FROM pro_tenant  
						WHERE status_tenant = 'Tenant' AND lokasi_tempat = '".$lokasi_tempat_loop."' AND no_toko = '".$no_toko_tenant."' ";

		$result3	= $this->db->query($ls_sql3);
		$data_tenant 	= $result3->row_array();

		$data['no_toko'] = $data_toko;
		$data['no_toko_limit'] =$data_toko_limit['no_toko'];
		$data['nama_tenant'] = $data_tenant['nama_tenant'];
		$data['jenis_usaha'] = $data_tenant['jenis_usaha'];
		$data['lokasi_tempat'] = $data_lokasi['lokasi_tempat'];
		$data['limit_id_lokasi'] = $data_lokasi['limit_id_lokasi'];
		$data['mode'] = 'switchAll';
		//var_dump($data['mode']);

		echo json_encode($data);
	}

	function getLoopNormal($type=null){

		$query1 = $this->db->query("SELECT id_pemakaian_air FROM (SELECT * FROM pro_pemakaian_air ORDER BY id_pemakaian_air DESC) WHERE rownum <=1");
				 				if ($query1->row_array()) {
				 					$getquery1 = $query1->row_array();
				 					$rowcount1 = $getquery1['id_pemakaian_air'];
				 					$autonum1 = $rowcount1 + 1;
				 				} else {
								$autonum1 = 1;
							}
		$data['no_auto'] = $autonum1;

		$lokasi_tempat = $this->input->post('lokasi_tempat');
		$no_toko = $this->input->post('no_toko');

		$ls_sql	= "	SELECT * FROM (SELECT ROWNUM as row_num, b.id_lokasi, b.lokasi_tempat, b.no_toko, b.nama_tenant, b.jenis_usaha FROM (
					SELECT id_lokasi, lokasi_tempat, no_toko, nama_tenant, jenis_usaha
					FROM pro_tenant 
					WHERE status_tenant = 'Tenant' AND lokasi_tempat = '".$lokasi_tempat."' 
					ORDER BY case when regexp_instr(NO_TOKO,'[0-9]+') = 1 then 
									 to_number(regexp_substr(NO_TOKO,'[0-9]+')) 
								else 
									 null 
								end ASC, NO_TOKO ASC) b
					) C
					where row_num = (SELECT * FROM (
					SELECT row_num FROM (
					select (ROWNUM + 1) as row_num, NO_TOKO from (SELECT NO_TOKO
					FROM pro_tenant 
					WHERE status_tenant = 'Tenant' AND lokasi_tempat = '".$lokasi_tempat."' 
					ORDER BY case when regexp_instr(NO_TOKO,'[0-9]+') = 1 then 
									 to_number(regexp_substr(NO_TOKO,'[0-9]+')) 
								else 
									 null 
								end ASC, NO_TOKO ASC) x) Z
					WHERE NO_TOKO = '".$no_toko."') XZ) ";

		$result	= $this->db->query($ls_sql);
		$data_toko = $result->row_array();

		$data['no_toko'] = $data_toko['no_toko'];
		$data['nama_tenant'] = $data_toko['nama_tenant'];
		$data['jenis_usaha'] = $data_toko['jenis_usaha'];
		$data['lokasi_tempat'] = $data_toko['lokasi_tempat'];
		$data['id_lokasi'] = $data_toko['id_lokasi'];
		$data['mode'] = 'normal';
		//var_dump($data['mode']);

		echo json_encode($data);

	}

	function getTenant($type=null) {
		$id = $this->input->post('option');
		$lokasi_tempat = $this->input->post('lokasi');

		//var_dump($id);

		$ls_sql	= " SELECT 	a.nama_tenant as nama_tenant,
							a.jenis_usaha as jenis_usaha,
							a.id_lokasi
					FROM pro_tenant a 
					JOIN pro_lokasi_tempat b ON a.id_lokasi = b.id_lokasi
					WHERE a.status_tenant = 'Tenant' AND a.lokasi_tempat = '".$lokasi_tempat."' AND a.no_toko = '".$id."'";

		$result	= $this->db->query($ls_sql);
		$data_tenant = $result->row_array(); 

		echo json_encode($data_tenant);

	}

	function ajax_post() {
		$is_error = 0;
		$ls_response = "";
		$ls_genid = "";

		$error = $this->validation();
		try
		{
			if (count( $error->Get() ) > 0) { $is_error = 1; }
			else
			{
				
					if (post("mode") == "create"){
						$no_toko = $this->input->post('no_toko');
					} else {
						$no_toko = $this->input->post('no_toko2');
					}

				// Fill Post Data
				$data	=  array(
					'id_pemakaian_air' => $this->input->post('id_pemakaian_air'),
					'no_toko' => $no_toko,
					'nama_tenant' => $this->input->post('nama_tenant'),
					'tanggal' => $this->input->post('periode_penagihan'),
					'no_air' => $this->input->post('no_air'),
					'meter_awal_pemakaian' => $this->input->post('meter_awal_pemakaian'),
					'meter_akhir_pemakaian' => $this->input->post('meter_akhir_pemakaian'),
					'total_meter_pemakaian' => $this->input->post('total_meter_pemakaian'),
					'lokasi_tempat' => $this->input->post('lokasi_tempat'),
					'nama_tenant' => $this->input->post('nama_tenant'),
					'jenis_usaha' => $this->input->post('jenis_usaha'),
					'id_lokasi' => $this->input->post('id_lokasi'),
					'status' => $this->input->post('status'),
					);
				// Set Primary Key Variable
				//$no = $data['no'];
				// $nama_tenant = $data['nama_tenant'];


				if (post("mode") == "create")
				{
					$this->KontrolPemakaian->Add($data);

					$ls_response = "Data Tersimpan";

				}
				else if (post("mode") == "edit")
				{
					//$data = array('no_toko' => $this->input->post('no_toko2'));
					$this->KontrolPemakaian->Update($data, array('id_pemakaian_air' => $this->input->post('id_pemakaian_air')));

					$ls_response = "Data Tersimpan [edited]";
				}
			
				
			}
		} catch(Exception $e) {
			$is_error = 1; $ls_response = $e->getMessage();
		}
		
		echo $this->PostResponse(json_encode(array(	'is_error'	=> $is_error,
													'fields'	=> $error->GetDictionary(),
													'msg'		=> $ls_response,
													'genid'		=> $ls_genid,
													'form_sender'=> post("form_sender"))
								));
	}

	private function validation() {
		$error = new ErrorList();

		// Get Error Template
		$tpl_empty = $this->MessageTemplate("ER001");
		$tpl_duplicate = $this->MessageTemplate("ER002");


		// Validation
		if (post("id_pemakaian_air") == '') {
			$error->Add("id_pemakaian_air", "id_pemakaian_air", $tpl_empty);
		} else if (post("mode") == "create" && intval($this->get_data("SELECT 1 FROM pro_pemakaian_air WHERE id_pemakaian_air = '".@ereg_replace("[^a-zA-Z0-9_-]", "",@strtoupper(@trim(post("id_pemakaian_air"))))."'")) > 0) {
			$error->Add("id_pemakaian_air", "id_pemakaian_air", $tpl_duplicate);
		}

		return $error;
	}

	function ajax_delete() {
		$ls_result	= "";
		$id_pemakaian_air	= get('id_pemakaian_air');
		if ($id_pemakaian_air != "") {
			// Delete Single
			$arr_rule	= array('id_pemakaian_air' => $id_pemakaian_air);
			$ls_result = $this->KontrolPemakaian->DeleteSingle($arr_rule);

		} else {
			$num_row = intval($this->data['num_row']['deleted']);
			
			for ($i = 0; $i < $num_row; $i++) {
				$row	= $this->data['deleted'][$i];
				
				$arr_rule	= array('id_pemakaian_air' => $row['id_pemakaian_air']);
				$ls_result .= $this->KontrolPemakaian->DeleteSingle($arr_rule);
			}
		}
		
		echo $ls_result;
	}

	function cetak(){
			
			// Load FPDF
			$this->load->helper('fpdf');
			
			// Set Data
			$data['data_list'] = $this->KontrolPemakaian->GetCetakList();
			
			// Load View
			$this->load->view('pro_kontrol_air/cetak', $data);
			
	}


}
?>